-- =====================================================
-- 02_insert_sample_data.sql
-- Sample Data Insertion - All data in single transaction
-- =====================================================

BEGIN;

-- Clear all existing data and reset sequences
TRUNCATE TABLE audit_logs, donor_clinics, offspring_tracking, shipments, order_items, orders, 
                sperm_vials, medical_screenings, recipients, donors, partner_clinics, staff 
RESTART IDENTITY CASCADE;

-- =====================================================
-- 02_insert_sample_data.sql
-- Sample Data Insertion with Unique IDs
-- All IDs are unique and sequential to avoid conflicts
-- =====================================================
-- 1) staff (22 rows: 20 active, 2 inactive)
INSERT INTO staff(staff_id, username, password_hash, full_name, role, is_active) VALUES
(1, 'admin', 'hash_admin', 'Alice Johnson', 'Admin', true),
(2, 'doctor1', 'hash_doc1', 'Dr. Michael Chen', 'Doctor', true),
(3, 'nurse1', 'hash_nurse1', 'Sarah Williams', 'Nurse', true),
(4, 'lab_tech1', 'hash_lab1', 'James Brown', 'Lab_Technician', true),
(5, 'coordinator1', 'hash_coord1', 'Emily Davis', 'Coordinator', true),
(6, 'doctor2', 'hash_doc2', 'Dr. Robert Martinez', 'Doctor', true),
(7, 'nurse2', 'hash_nurse2', 'Jessica Garcia', 'Nurse', true),
(8, 'lab_tech2', 'hash_lab2', 'David Rodriguez', 'Lab_Technician', true),
(9, 'coordinator2', 'hash_coord2', 'Lisa Anderson', 'Coordinator', true),
(10, 'admin2', 'hash_admin2', 'Tom Wilson', 'Admin', true),
(11, 'doctor3', 'hash_doc3', 'Dr. Jennifer Taylor', 'Doctor', true),
(12, 'nurse3', 'hash_nurse3', 'Karen Thomas', 'Nurse', true),
(13, 'lab_tech3', 'hash_lab3', 'Christopher Moore', 'Lab_Technician', true),
(14, 'coordinator3', 'hash_coord3', 'Amanda Jackson', 'Coordinator', true),
(15, 'doctor4', 'hash_doc4', 'Dr. William White', 'Doctor', true),
(16, 'nurse4', 'hash_nurse4', 'Michelle Harris', 'Nurse', true),
(17, 'lab_tech4', 'hash_lab4', 'Daniel Martin', 'Lab_Technician', true),
(18, 'coordinator4', 'hash_coord4', 'Patricia Thompson', 'Coordinator', true),
(19, 'admin3', 'hash_admin3', 'Richard Lee', 'Admin', true),
(20, 'doctor5', 'hash_doc5', 'Dr. Linda Robinson', 'Doctor', true),
(21, 'coord_old', 'hash_old', 'Nancy Brown', 'Coordinator', false),
(22, 'admin_former', 'hash_former', 'Daniel Lee', 'Admin', false);
-- 2) donors (23 rows: 20 active, 3 inactive)
INSERT INTO donors(donor_id, donor_code, full_name, date_of_birth, ethnicity, marital_status, blood_type, height_cm, weight_kg, eye_color, hair_color, education_level, occupation, contact_email, contact_phone, is_active)
VALUES
(1,'D001','John Smith','1995-03-15','Caucasian','Single','A+',180,75.50,'Blue','Blonde','Bachelor','Engineer','john.s@example.com','+12025550101',true),
(2,'D002','Michael Johnson','1992-07-22','Caucasian','Single','O+',175,72.00,'Green','Brown','Master','Software Developer','michael.j@example.com','+12025550102',true),
(3,'D003','David Williams','1994-11-08','Caucasian','Single','B+',182,78.30,'Brown','Black','PhD','Research Scientist','david.w@example.com','+12025550103',true),
(4,'D004','James Brown','1993-05-19','African American','Single','AB+',185,82.00,'Brown','Black','Bachelor','Teacher','james.b@example.com','+12025550104',true),
(5,'D005','Robert Davis','1996-09-30','Caucasian','Single','A-',178,74.80,'Blue','Brown','Master','Architect','robert.d@example.com','+12025550105',true),
(6,'D006','Christopher Wilson','1991-12-12','Caucasian','Married','O-',183,80.50,'Hazel','Blonde','Bachelor','Business Analyst','chris.w@example.com','+12025550106',true),
(7,'D007','Daniel Martinez','1997-04-25','Hispanic','Single','B-',176,71.20,'Brown','Brown','Bachelor','Graphic Designer','daniel.m@example.com','+12025550107',true),
(8,'D008','Matthew Anderson','1990-08-14','Caucasian','Single','A+',181,77.00,'Blue','Red','PhD','Professor','matthew.a@example.com','+12025550108',true),
(9,'D009','Anthony Taylor','1994-02-28','Caucasian','Single','O+',179,76.40,'Green','Brown','Master','Financial Analyst','anthony.t@example.com','+12025550109',true),
(10,'D010','Mark Thomas','1995-06-17','Caucasian','Single','AB-',177,73.60,'Blue','Black','Bachelor','Marketing Manager','mark.t@example.com','+12025550110',true),
(11,'D011','Paul Jackson','1993-10-03','African American','Single','B+',184,81.30,'Brown','Black','Master','IT Consultant','paul.j@example.com','+12025550111',true),
(12,'D012','Steven White','1996-01-20','Caucasian','Single','A+',180,75.90,'Blue','Blonde','Bachelor','Sales Manager','steven.w@example.com','+12025550112',true),
(13,'D013','Andrew Harris','1992-11-07','Caucasian','Married','O-',178,74.10,'Green','Brown','PhD','Data Scientist','andrew.h@example.com','+12025550113',true),
(14,'D014','Joshua Martin','1994-03-31','Hispanic','Single','A-',182,79.50,'Brown','Black','Master','Civil Engineer','joshua.m@example.com','+12025550114',true),
(15,'D015','Kevin Thompson','1991-05-25','Caucasian','Single','B+',179,76.70,'Hazel','Brown','Bachelor','Project Manager','kevin.t@example.com','+12025550115',true),
(16,'D016','Brian Garcia','1995-09-12','Hispanic','Single','AB+',186,84.20,'Brown','Black','Bachelor','Professional Athlete','brian.g@example.com','+12025550116',true),
(17,'D017','George Rodriguez','1997-02-08','Hispanic','Single','O+',174,69.80,'Brown','Brown','Bachelor','Web Developer','george.r@example.com','+12025550117',true),
(18,'D018','Edward Lee','1993-06-29','Asian','Single','A+',175,70.40,'Brown','Black','PhD','Research Fellow','edward.l@example.com','+12025550118',true),
(19,'D019','Ronald Walker','1996-12-14','Caucasian','Single','B-',181,77.90,'Blue','Blonde','Master','Consultant','ronald.w@example.com','+12025550119',true),
(20,'D020','Timothy Hall','1994-08-22','Caucasian','Single','A+',183,79.30,'Green','Brown','Bachelor','Entrepreneur','timothy.h@example.com','+12025550120',true),
(21,'D021','Igor Petrov','1990-07-12','Caucasian','Married','O+',179,76.00,'Blue','Blonde','Master','Economist','igor.p@example.com','+79165550121',false),
(22,'D022','Raj Patel','1987-12-25','Asian','Married','B-',172,70.50,'Brown','Black','Master','Doctor','raj.p@example.com','+919875550122',false),
(23,'D023','Ahmed Al-Farsi','1994-08-03','Middle Eastern','Single','AB+',180,81.20,'Brown','Black','Bachelor','Businessman','ahmed.a@example.com','+971505550123',false);
-- 3) recipients (23 rows)
INSERT INTO recipients(recipient_id, full_name, date_of_birth, contact_email, contact_phone, address)
VALUES
(1,'Emma Wilson','1988-04-12','emma.w@example.com','+12025550201','123 Oak Street, Boston, MA 02101'),
(2,'Olivia Martinez','1990-08-25','olivia.m@example.com','+12025550202','456 Maple Ave, Los Angeles, CA 90001'),
(3,'Sophia Anderson','1985-12-03','sophia.a@example.com','+12025550203','789 Pine Road, Chicago, IL 60601'),
(4,'Ava Thomas','1992-03-17','ava.t@example.com','+12025550204','321 Elm Street, Houston, TX 77001'),
(5,'Isabella Taylor','1987-06-28','isabella.t@example.com','+12025550205','654 Cedar Lane, Phoenix, AZ 85001'),
(6,'Mia Hernandez','1991-09-14','mia.h@example.com','+12025550206','987 Birch Court, Philadelphia, PA 19101'),
(7,'Charlotte Moore','1989-11-22','charlotte.m@example.com','+12025550207','147 Willow Drive, San Antonio, TX 78201'),
(8,'Amelia Jackson','1993-02-08','amelia.j@example.com','+12025550208','258 Spruce Avenue, San Diego, CA 92101'),
(9,'Harper Lee','1986-05-19','harper.l@example.com','+12025550209','369 Ash Street, Dallas, TX 75201'),
(10,'Evelyn White','1994-07-30','evelyn.w@example.com','+12025550210','741 Poplar Road, San Jose, CA 95101'),
(11,'Abigail Harris','1988-10-11','abigail.h@example.com','+12025550211','852 Magnolia Lane, Austin, TX 78701'),
(12,'Emily Clark','1990-01-24','emily.c@example.com','+12025550212','963 Chestnut Court, Jacksonville, FL 32099'),
(13,'Elizabeth Lewis','1992-04-05','elizabeth.l@example.com','+12025550213','159 Dogwood Drive, Fort Worth, TX 76101'),
(14,'Sofia Robinson','1987-06-16','sofia.r@example.com','+12025550214','267 Hickory Avenue, Columbus, OH 43085'),
(15,'Avery Walker','1991-08-27','avery.w@example.com','+12025550215','378 Walnut Street, Charlotte, NC 28201'),
(16,'Ella Hall','1989-11-08','ella.h@example.com','+12025550216','489 Sycamore Road, San Francisco, CA 94102'),
(17,'Scarlett Allen','1993-01-19','scarlett.a@example.com','+442075550217','10 Downing Street, London SW1A 2AA, UK'),
(18,'Grace Young','1986-03-30','grace.y@example.com','+14165550218','100 Queen Street West, Toronto ON M5H 2N2, Canada'),
(19,'Chloe King','1990-05-11','chloe.k@example.com','+81335550219','1-1-1 Chiyoda, Tokyo 100-0001, Japan'),
(20,'Victoria Scott','1988-07-22','victoria.s@example.com','+61295550220','1 Harbour Street, Sydney NSW 2000, Australia'),
(21,'Madison Green','1992-09-03','madison.g@example.com','+6567890221','1 Marina Boulevard, Singapore 018989'),
(22,'Luna Adams','1987-11-14','luna.a@example.com','+48225550222','ul. Nowy Swiat 1, 00-001 Warsaw, Poland'),
(23,'Aria Baker','1991-12-25','aria.b@example.com','+34915550223','Calle Mayor 1, 28013 Madrid, Spain');
-- 4) partner_clinics (22 rows)
INSERT INTO partner_clinics(clinic_id, clinic_name, address, contact_person, contact_phone)
VALUES
(1,'Harmony Fertility Center','100 Medical Plaza, Boston, MA 02115','Dr. Sarah Johnson','+16175551001'),
(2,'Hope Reproductive Medicine','200 Health Avenue, Los Angeles, CA 90024','Dr. Michael Chen','+13105551002'),
(3,'Sunrise Fertility Clinic','300 Wellness Street, Chicago, IL 60611','Dr. Emily Rodriguez','+13125551003'),
(4,'LifeBirth Center','400 Care Lane, Houston, TX 77030','Dr. James Wilson','+17135551004'),
(5,'BrightFuture Fertility','500 Hope Road, Phoenix, AZ 85012','Dr. Lisa Anderson','+16025551005'),
(6,'Gentle Care Clinic','600 Family Drive, Philadelphia, PA 19104','Dr. Robert Martinez','+12155551006'),
(7,'Serene Health Center','700 Peace Avenue, San Antonio, TX 78205','Dr. Jennifer Taylor','+12105551007'),
(8,'Harbor Fertility','800 Bay Street, San Diego, CA 92101','Dr. William Brown','+16195551008'),
(9,'Emerald Fertility Clinic','900 Green Lane, Dallas, TX 75201','Dr. Patricia Davis','+12145551009'),
(10,'Golden Gate Reproductive','1000 Bridge Road, San Francisco, CA 94104','Dr. Christopher Lee','+14155551010'),
(11,'NewLife Fertility Center','1100 Innovation Drive, Austin, TX 78701','Dr. Amanda White','+15125551011'),
(12,'NewEra IVF Clinic','1200 Future Boulevard, Seattle, WA 98101','Dr. Daniel Kim','+12065551012'),
(13,'Manhattan Reproductive Medicine','1300 Park Avenue, New York, NY 10021','Dr. Jessica Garcia','+12125551013'),
(14,'Pacific Fertility Institute','1400 Ocean Drive, San Jose, CA 95113','Dr. Thomas Anderson','+14085551014'),
(15,'Royal Oak Fertility','1500 Heritage Lane, Detroit, MI 48201','Dr. Karen Johnson','+13135551015'),
(16,'London Fertility Clinic','25 Harley Street, London W1G 9QW, UK','Dr. Emma Thompson','+442075551016'),
(17,'Toronto Reproductive Center','200 Bay Street, Toronto ON M5J 2J2, Canada','Dr. Michael Brown','+14165551017'),
(18,'Sydney IVF Center','50 Pitt Street, Sydney NSW 2000, Australia','Dr. Sophie Wilson','+61295551018'),
(19,'Tokyo Fertility Hospital','3-1-1 Shibuya, Tokyo 150-0002, Japan','Dr. Yuki Tanaka','+81335551019'),
(20,'Berlin Reproductive Medicine','Unter den Linden 1, 10117 Berlin, Germany','Dr. Hans Schmidt','+493025551020'),
(21,'Singapore Fertility Clinic','3 Mount Elizabeth, Singapore 228510','Dr. Wei Chen','+6562345551021'),
(22,'Paris Fertility Institute','25 Rue du Faubourg Saint-Honoré, 75008 Paris, France','Dr. Sophie Dubois','+33142551022');
-- 5) sperm_vials (48 rows: Used(22) + Reserved(14) + Available(10) + Discarded(2))
INSERT INTO sperm_vials(vial_id, donor_id, collection_date, motility_percent, sperm_count_mil, volume_ml, status, storage_location, quarantine_release_date, notes)
VALUES
-- USED vials (22) from completed orders
(1,1,'2024-01-10',75,50.00,1.00,'Used','Tank A, Rack 1, Box 1','2024-07-10','Order #1 completed'),
(2,2,'2024-02-12',68,42.50,1.10,'Used','Tank A, Rack 1, Box 2','2024-08-12','Order #2 completed'),
(3,3,'2024-03-15',80,55.20,1.15,'Used','Tank A, Rack 1, Box 3','2024-09-15','Order #3 completed'),
(4,1,'2023-11-05',82,58.00,1.30,'Used','Tank A, Rack 1, Box 4','2024-05-05','Order #11 completed'),
(5,3,'2023-11-08',77,53.50,1.20,'Used','Tank A, Rack 1, Box 5','2024-05-08','Order #11 completed'),
(6,5,'2024-02-18',71,48.30,1.00,'Used','Tank A, Rack 2, Box 1','2024-08-18','Order #12 completed'),
(7,7,'2024-02-20',65,45.80,0.95,'Used','Tank A, Rack 2, Box 2','2024-08-20','Order #12 completed'),
(8,9,'2024-02-22',70,49.00,1.05,'Used','Tank A, Rack 2, Box 3','2024-08-22','Order #12 completed'),
(9,20,'2024-07-10',88,68.40,1.50,'Used','Tank A, Rack 2, Box 4','2025-01-10','Order #16 completed'),
(10,16,'2024-07-12',92,75.20,1.60,'Used','Tank A, Rack 2, Box 5','2025-01-12','Order #16 completed'),
-- Additional USED vials (12) from new historical completed orders #23-34
(37,4,'2023-06-15',78,54.20,1.15,'Used','Tank A, Rack 3, Box 1','2023-12-15','Order #23 completed'),
(38,6,'2023-07-20',72,48.80,1.05,'Used','Tank A, Rack 3, Box 2','2024-01-20','Order #24 completed'),
(39,8,'2023-08-10',81,59.50,1.25,'Used','Tank A, Rack 3, Box 3','2024-02-10','Order #25 completed'),
(40,10,'2023-09-05',75,52.30,1.12,'Used','Tank A, Rack 3, Box 4','2024-03-05','Order #26 completed'),
(41,2,'2023-10-12',69,46.70,1.08,'Used','Tank A, Rack 3, Box 5','2024-04-12','Order #27 completed'),
(42,4,'2023-11-18',83,60.40,1.28,'Used','Tank A, Rack 4, Box 1','2024-05-18','Order #28 completed'),
(43,6,'2024-01-25',76,53.90,1.18,'Used','Tank A, Rack 4, Box 2','2024-07-25','Order #29 completed'),
(44,8,'2024-02-14',70,49.20,1.10,'Used','Tank A, Rack 4, Box 3','2024-08-14','Order #30 completed'),
(45,10,'2024-03-20',84,61.80,1.32,'Used','Tank A, Rack 4, Box 4','2024-09-20','Order #31 completed'),
(46,12,'2024-04-05',77,55.60,1.20,'Used','Tank A, Rack 4, Box 5','2024-10-05','Order #32 completed'),
(47,14,'2024-05-10',73,50.40,1.14,'Used','Tank A, Rack 5, Box 1','2024-11-10','Order #33 completed'),
(48,15,'2024-06-15',79,57.30,1.22,'Used','Tank A, Rack 5, Box 2','2024-12-15','Order #34 completed'),
-- RESERVED vials (14) for active orders
(11,4,'2025-01-15',73,51.80,1.10,'Reserved','Tank B, Rack 1, Box 1','2025-07-15','Order #4 shipped'),
(12,5,'2025-02-10',69,47.20,1.00,'Reserved','Tank B, Rack 1, Box 2','2025-08-10','Order #5 paid'),
(13,6,'2025-02-20',78,54.60,1.20,'Reserved','Tank B, Rack 1, Box 3','2025-08-20','Order #6 pending'),
(14,7,'2025-03-05',65,44.80,0.98,'Reserved','Tank B, Rack 1, Box 4','2025-09-05','Order #7 pending'),
(15,8,'2025-03-18',81,59.30,1.25,'Reserved','Tank B, Rack 1, Box 5','2025-09-18','Order #8 paid'),
(16,16,'2025-09-01',85,66.50,1.40,'Reserved','Tank B, Rack 2, Box 1','2026-03-01','Order #13 pending'),
(17,18,'2025-08-20',79,56.40,1.30,'Reserved','Tank B, Rack 2, Box 2','2026-02-20','Order #17 shipped'),
(18,1,'2025-09-10',76,52.70,1.15,'Reserved','Tank B, Rack 2, Box 3','2026-03-10','Order #18 shipped'),
(19,3,'2025-09-12',72,50.20,1.10,'Reserved','Tank B, Rack 2, Box 4','2026-03-12','Order #18 shipped'),
(20,5,'2025-09-28',68,46.80,1.05,'Reserved','Tank B, Rack 2, Box 5','2026-03-28','Order #19 paid'),
(21,7,'2025-09-30',74,53.10,1.12,'Reserved','Tank B, Rack 3, Box 1','2026-03-30','Order #19 paid'),
(22,9,'2025-10-05',71,49.60,1.08,'Reserved','Tank B, Rack 3, Box 2','2026-04-05','Order #20 pending'),
(23,2,'2025-10-12',67,45.30,1.02,'Reserved','Tank B, Rack 3, Box 3','2026-04-12','Order #21 pending'),
(24,4,'2025-10-14',70,48.50,1.06,'Reserved','Tank B, Rack 3, Box 4','2026-04-14','Order #21 pending'),
-- AVAILABLE vials (10) 6 from cancelled + 4 fresh
(25,9,'2025-04-20',66,44.10,1.00,'Available','Tank C, Rack 1, Box 1','2025-10-20','Released from cancelled order'),
(26,10,'2025-05-10',72,50.80,1.15,'Available','Tank C, Rack 1, Box 2','2025-11-10','Released from cancelled order'),
(27,17,'2025-06-18',58,38.90,0.90,'Available','Tank C, Rack 1, Box 3','2025-12-18','Released from cancelled order'),
(28,18,'2025-08-05',75,52.40,1.12,'Available','Tank C, Rack 1, Box 4','2026-02-05','Released from cancelled order'),
(29,19,'2025-08-07',69,47.60,1.08,'Available','Tank C, Rack 1, Box 5','2026-02-07','Released from cancelled order'),
(30,6,'2025-09-25',64,43.20,0.98,'Available','Tank C, Rack 2, Box 1','2026-03-25','Released from cancelled order'),
(31,1,'2025-10-20',89,72.30,1.45,'Available','Tank D, Rack 1, Box 1','2026-04-20','Fresh inventory - premium'),
(32,3,'2025-10-22',86,68.50,1.38,'Available','Tank D, Rack 1, Box 2','2026-04-22','Fresh inventory - excellent'),
(33,5,'2025-10-18',80,59.20,1.28,'Available','Tank D, Rack 1, Box 3','2026-04-18','Fresh inventory - good'),
(34,7,'2025-10-15',76,54.80,1.20,'Available','Tank D, Rack 1, Box 4','2026-04-15','Fresh inventory - ready'),
-- DISCARDED vials (2)
(35,11,'2024-06-15',22,16.80,0.55,'Discarded','Tank E, Rack 1, Box 1','2024-12-15','Failed QC - motility too low'),
(36,13,'2024-09-20',15,11.20,0.40,'Discarded','Tank E, Rack 1, Box 2','2025-03-20','Failed post-thaw viability');
-- 6) medical_screenings (100 rows - each donor has 4-5 screenings)
-- Standard tests: HIV, Hepatitis B, Hepatitis C, Syphilis, Semen Analysis
INSERT INTO medical_screenings(screening_id, donor_id, screening_date, test_name, result, lab_name, notes)
VALUES
-- Donor 1 (5 tests)
(1,1,'2024-12-01','HIV Test','Negative','Central Lab',''),
(2,1,'2024-12-01','Hepatitis B','Negative','Central Lab',''),
(3,1,'2024-12-01','Hepatitis C','Negative','Central Lab',''),
(4,1,'2024-12-01','Syphilis Test','Negative','Central Lab',''),
(5,1,'2024-12-03','Semen Analysis','Normal','Central Lab','Motility 75%, Count 50M/ml'),
-- Donor 2 (5 tests)
(6,2,'2024-12-02','HIV Test','Negative','MedTest Labs',''),
(7,2,'2024-12-02','Hepatitis B','Negative','MedTest Labs',''),
(8,2,'2024-12-02','Hepatitis C','Negative','MedTest Labs',''),
(9,2,'2024-12-02','Syphilis Test','Negative','MedTest Labs',''),
(10,2,'2024-12-04','Semen Analysis','Good','MedTest Labs','Motility 68%, Count 42M/ml'),
-- Donor 3 (5 tests)
(11,3,'2024-12-03','HIV Test','Negative','Health Diagnostics',''),
(12,3,'2024-12-03','Hepatitis B','Negative','Health Diagnostics',''),
(13,3,'2024-12-03','Hepatitis C','Negative','Health Diagnostics',''),
(14,3,'2024-12-03','Syphilis Test','Negative','Health Diagnostics',''),
(15,3,'2024-12-05','Semen Analysis','Excellent','Health Diagnostics','Motility 80%, Count 55M/ml'),
-- Donor 4 (4 tests)
(16,4,'2024-12-04','HIV Test','Negative','Central Lab',''),
(17,4,'2024-12-04','Hepatitis B','Negative','Central Lab',''),
(18,4,'2024-12-04','Syphilis Test','Negative','Central Lab',''),
(19,4,'2024-12-06','Semen Analysis','Normal','Central Lab','Motility 73%, Count 51M/ml'),
-- Donor 5 (5 tests)
(20,5,'2024-12-05','HIV Test','Negative','MedTest Labs',''),
(21,5,'2024-12-05','Hepatitis B','Negative','MedTest Labs',''),
(22,5,'2024-12-05','Hepatitis C','Negative','MedTest Labs',''),
(23,5,'2024-12-05','Syphilis Test','Negative','MedTest Labs',''),
(24,5,'2024-12-07','Semen Analysis','Good','MedTest Labs','Motility 69%, Count 47M/ml'),
-- Donor 6-10 (4 tests each = 20 tests)
(25,6,'2024-12-06','HIV Test','Negative','Health Diagnostics',''),
(26,6,'2024-12-06','Hepatitis B','Negative','Health Diagnostics',''),
(27,6,'2024-12-06','Syphilis Test','Negative','Health Diagnostics',''),
(28,6,'2024-12-08','Semen Analysis','Good','Health Diagnostics','Motility 78%, Count 54M/ml'),
(29,7,'2024-12-07','HIV Test','Negative','Central Lab',''),
(30,7,'2024-12-07','Hepatitis B','Negative','Central Lab',''),
(31,7,'2024-12-07','Syphilis Test','Negative','Central Lab',''),
(32,7,'2024-12-09','Semen Analysis','Normal','Central Lab','Motility 65%, Count 44M/ml'),
(33,8,'2024-12-08','HIV Test','Negative','MedTest Labs',''),
(34,8,'2024-12-08','Hepatitis B','Negative','MedTest Labs',''),
(35,8,'2024-12-08','Syphilis Test','Negative','MedTest Labs',''),
(36,8,'2024-12-10','Semen Analysis','Excellent','MedTest Labs','Motility 81%, Count 59M/ml'),
(37,9,'2024-12-09','HIV Test','Negative','Health Diagnostics',''),
(38,9,'2024-12-09','Hepatitis B','Negative','Health Diagnostics',''),
(39,9,'2024-12-09','Syphilis Test','Negative','Health Diagnostics',''),
(40,9,'2024-12-11','Semen Analysis','Good','Health Diagnostics','Motility 70%, Count 49M/ml'),
(41,10,'2024-12-10','HIV Test','Negative','Central Lab',''),
(42,10,'2024-12-10','Hepatitis B','Negative','Central Lab',''),
(43,10,'2024-12-10','Syphilis Test','Negative','Central Lab',''),
(44,10,'2024-12-12','Semen Analysis','Normal','Central Lab','Motility 72%, Count 50M/ml'),
-- Donor 11-15 (4 tests each = 20 tests)
(45,11,'2024-12-11','HIV Test','Negative','MedTest Labs',''),
(46,11,'2024-12-11','Hepatitis B','Negative','MedTest Labs',''),
(47,11,'2024-12-11','Syphilis Test','Negative','MedTest Labs',''),
(48,11,'2024-12-13','Semen Analysis','Good','MedTest Labs','Motility 76%, Count 53M/ml'),
(49,12,'2024-12-12','HIV Test','Negative','Health Diagnostics',''),
(50,12,'2024-12-12','Hepatitis B','Negative','Health Diagnostics',''),
(51,12,'2024-12-12','Syphilis Test','Negative','Health Diagnostics',''),
(52,12,'2024-12-14','Semen Analysis','Normal','Health Diagnostics','Motility 71%, Count 48M/ml'),
(53,13,'2024-12-13','HIV Test','Negative','Central Lab',''),
(54,13,'2024-12-13','Hepatitis B','Negative','Central Lab',''),
(55,13,'2024-12-13','Syphilis Test','Negative','Central Lab',''),
(56,13,'2024-12-15','Semen Analysis','Poor','Central Lab','Motility 15%, Count 11M/ml - Discarded'),
(57,14,'2024-12-14','HIV Test','Negative','MedTest Labs',''),
(58,14,'2024-12-14','Hepatitis B','Negative','MedTest Labs',''),
(59,14,'2024-12-14','Syphilis Test','Negative','MedTest Labs',''),
(60,14,'2024-12-16','Semen Analysis','Good','MedTest Labs','Motility 74%, Count 52M/ml'),
(61,15,'2024-12-15','HIV Test','Negative','Health Diagnostics',''),
(62,15,'2024-12-15','Hepatitis B','Negative','Health Diagnostics',''),
(63,15,'2024-12-15','Syphilis Test','Negative','Health Diagnostics',''),
(64,15,'2024-12-17','Semen Analysis','Normal','Health Diagnostics','Motility 77%, Count 54M/ml'),
-- Donor 16-20 (4 tests each = 20 tests)
(65,16,'2024-12-16','HIV Test','Negative','Central Lab',''),
(66,16,'2024-12-16','Hepatitis B','Negative','Central Lab',''),
(67,16,'2024-12-16','Syphilis Test','Negative','Central Lab',''),
(68,16,'2024-12-18','Semen Analysis','Excellent','Central Lab','Motility 92%, Count 75M/ml'),
(69,17,'2024-12-17','HIV Test','Negative','MedTest Labs',''),
(70,17,'2024-12-17','Hepatitis B','Negative','MedTest Labs',''),
(71,17,'2024-12-17','Syphilis Test','Negative','MedTest Labs',''),
(72,17,'2024-12-19','Semen Analysis','Low','MedTest Labs','Motility 58%, Count 38M/ml'),
(73,18,'2024-12-18','HIV Test','Negative','Health Diagnostics',''),
(74,18,'2024-12-18','Hepatitis B','Negative','Health Diagnostics',''),
(75,18,'2024-12-18','Syphilis Test','Negative','Health Diagnostics',''),
(76,18,'2024-12-20','Semen Analysis','Good','Health Diagnostics','Motility 79%, Count 56M/ml'),
(77,19,'2024-12-19','HIV Test','Negative','Central Lab',''),
(78,19,'2024-12-19','Hepatitis B','Negative','Central Lab',''),
(79,19,'2024-12-19','Syphilis Test','Negative','Central Lab',''),
(80,19,'2024-12-21','Semen Analysis','Normal','Central Lab','Motility 69%, Count 47M/ml'),
(81,20,'2024-12-20','HIV Test','Negative','MedTest Labs',''),
(82,20,'2024-12-20','Hepatitis B','Negative','MedTest Labs',''),
(83,20,'2024-12-20','Syphilis Test','Negative','MedTest Labs',''),
(84,20,'2024-12-22','Semen Analysis','Excellent','MedTest Labs','Motility 88%, Count 68M/ml'),
-- Donor 21-23 (4 tests each = 12 tests)
(85,21,'2024-12-21','HIV Test','Negative','Health Diagnostics',''),
(86,21,'2024-12-21','Hepatitis B','Negative','Health Diagnostics',''),
(87,21,'2024-12-21','Syphilis Test','Negative','Health Diagnostics',''),
(88,21,'2024-12-23','Semen Analysis','Normal','Health Diagnostics','Motility 76%, Count 52M/ml'),
(89,22,'2024-12-22','HIV Test','Negative','Central Lab',''),
(90,22,'2024-12-22','Hepatitis B','Negative','Central Lab',''),
(91,22,'2024-12-22','Syphilis Test','Negative','Central Lab',''),
(92,22,'2024-12-24','Semen Analysis','Good','Central Lab','Motility 72%, Count 50M/ml'),
(93,23,'2024-12-23','HIV Test','Negative','MedTest Labs',''),
(94,23,'2024-12-23','Hepatitis B','Negative','MedTest Labs',''),
(95,23,'2024-12-23','Syphilis Test','Negative','MedTest Labs',''),
(96,23,'2024-12-25','Semen Analysis','Normal','MedTest Labs','Motility 70%, Count 48M/ml'),
-- Additional comprehensive screenings for select donors (4 more)
(97,1,'2024-12-15','Genetic Screening','Normal','GenLab','No hereditary diseases detected'),
(98,3,'2024-12-16','Chromosome Analysis','Normal','GenLab','46,XY'),
(99,8,'2024-12-17','Cystic Fibrosis Carrier','Negative','GenLab','Not a carrier'),
(100,16,'2024-12-18','Blood Type Confirmation','AB+','Central Lab','Confirmed');
-- 7) orders (34 rows: 18 Completed + 3 Shipped + 2 Paid + 5 Pending + 6 Cancelled)
INSERT INTO orders(order_id, recipient_id, clinic_id, order_date, status, total_price, staff_id)
VALUES
(1,1,1,'2024-01-15','Completed',800.00,3),
(2,2,2,'2024-06-20','Completed',820.00,6),
(3,3,3,'2023-09-01','Completed',780.00,8),
(4,4,4,'2025-02-10','Shipped',850.00,10),
(5,5,5,'2025-03-05','Paid',790.00,3),
(6,6,6,'2025-03-15','Pending',800.00,6),
(7,7,7,'2025-04-01','Pending',760.00,8),
(8,8,8,'2025-04-12','Paid',830.00,10),
(9,9,9,'2025-05-03','Cancelled',800.00,3),
(10,10,10,'2025-05-20','Cancelled',770.00,6),
(11,11,11,'2023-12-10','Completed',1600.00,11),
(12,12,12,'2024-03-22','Completed',2400.00,14),
(13,7,7,'2025-09-20','Pending',795.00,17),
(14,8,8,'2025-07-12','Cancelled',820.00,20),
(15,19,19,'2025-08-25','Cancelled',1600.00,21),
(16,13,13,'2024-08-15','Completed',1620.00,16),
(17,14,14,'2025-09-05','Shipped',810.00,12),
(18,15,15,'2025-09-12','Shipped',1650.00,13),
(19,16,16,'2025-10-01','Paid',1600.00,15),
(20,17,17,'2025-10-08','Pending',840.00,18),
(21,18,18,'2025-10-15','Pending',1590.00,19),
(22,20,20,'2025-09-30','Cancelled',785.00,22),
-- New historical completed orders #23-34 (12 rows)
(23,4,4,'2023-06-20','Completed',795.00,2),
(24,5,5,'2023-07-25','Completed',810.00,3),
(25,6,6,'2023-08-15','Completed',825.00,4),
(26,7,7,'2023-09-10','Completed',800.00,5),
(27,8,8,'2023-10-18','Completed',790.00,6),
(28,9,9,'2023-11-22','Completed',815.00,7),
(29,10,10,'2024-02-01','Completed',805.00,8),
(30,14,14,'2024-02-20','Completed',795.00,9),
(31,15,15,'2024-03-25','Completed',830.00,10),
(32,17,17,'2024-04-10','Completed',820.00,11),
(33,18,18,'2024-05-15','Completed',800.00,12),
(34,19,19,'2024-06-20','Completed',810.00,13);
-- 8) order_items (42 rows - each order has 1-3 vials)
INSERT INTO order_items(order_item_id, order_id, vial_id, quantity, price_per_unit)
VALUES
(1,1,1,1,800.00),
(2,2,2,1,820.00),
(3,3,3,1,780.00),
(4,4,11,1,850.00),
(5,5,12,1,790.00),
(6,6,13,1,800.00),
(7,7,14,1,760.00),
(8,8,15,1,830.00),
(9,9,25,1,800.00),
(10,10,26,1,770.00),
(11,11,4,1,800.00),
(12,11,5,1,800.00),
(13,12,6,1,800.00),
(14,12,7,1,800.00),
(15,12,8,1,800.00),
(16,13,16,1,795.00),
(17,14,27,1,820.00),
(18,15,28,1,800.00),
(19,15,29,1,800.00),
(20,16,9,1,810.00),
(21,16,10,1,810.00),
(22,17,17,1,810.00),
(23,18,18,1,825.00),
(24,18,19,1,825.00),
(25,19,20,1,800.00),
(26,19,21,1,800.00),
(27,20,22,1,840.00),
(28,21,23,1,795.00),
(29,21,24,1,795.00),
(30,22,30,1,785.00),
-- New order_items for historical orders #23-34 (12 rows)
(31,23,37,1,795.00),
(32,24,38,1,810.00),
(33,25,39,1,825.00),
(34,26,40,1,800.00),
(35,27,41,1,790.00),
(36,28,42,1,815.00),
(37,29,43,1,805.00),
(38,30,44,1,795.00),
(39,31,45,1,830.00),
(40,32,46,1,820.00),
(41,33,47,1,800.00),
(42,34,48,1,810.00);
-- 9) shipments (34 rows - for all non-cancelled orders)
INSERT INTO shipments(shipment_id, order_id, ship_date, delivery_date, tracking_number, status, staff_id)
VALUES
(1,1,'2024-01-16','2024-01-20','TRK000001','Delivered',1),
(2,2,'2024-06-21','2024-06-26','TRK000002','Delivered',2),
(3,3,'2023-09-02','2023-09-08','TRK000003','Delivered',3),
(4,4,'2025-02-11',NULL,'TRK000004','In Transit',4),
(5,5,'2025-03-06',NULL,'TRK000005','Preparing',5),
(6,8,'2025-04-13',NULL,'TRK000008','Preparing',8),
(7,11,'2023-12-11','2023-12-18','TRK000011','Delivered',5),
(8,12,'2024-03-23','2024-04-02','TRK000012','Delivered',7),
(9,16,'2024-08-16','2024-08-28','TRK000016','Delivered',9),
(10,17,'2025-09-06',NULL,'TRK000017','In Transit',11),
(11,18,'2025-09-13',NULL,'TRK000018','In Transit',12),
(12,19,'2025-10-02',NULL,'TRK000019','Preparing',13),
(13,9,'2025-05-04','2025-05-10','TRK000009','Delivered',9),
(14,14,'2025-07-13',NULL,'TRK000014','Failed',14),
(15,15,'2025-08-26',NULL,'TRK000015','Failed',15),
(16,22,'2025-10-01',NULL,'TRK000022','Failed',16),
(17,10,'2025-05-21',NULL,'TRK000010','Failed',10),
(18,6,'2025-03-16',NULL,'TRK000006','Preparing',6),
(19,7,'2025-04-02',NULL,'TRK000007','Preparing',7),
(20,13,'2025-09-21',NULL,'TRK000013','Preparing',17),
(21,20,'2025-10-09',NULL,'TRK000020','Preparing',18),
(22,21,'2025-10-16',NULL,'TRK000021','Preparing',19),
-- New shipments for historical completed orders #23-34 (12 rows - all Delivered)
(23,23,'2023-06-21','2023-06-27','TRK000023','Delivered',2),
(24,24,'2023-07-26','2023-08-02','TRK000024','Delivered',3),
(25,25,'2023-08-16','2023-08-23','TRK000025','Delivered',4),
(26,26,'2023-09-11','2023-09-18','TRK000026','Delivered',5),
(27,27,'2023-10-19','2023-10-26','TRK000027','Delivered',6),
(28,28,'2023-11-23','2023-11-30','TRK000028','Delivered',7),
(29,29,'2024-02-02','2024-02-09','TRK000029','Delivered',8),
(30,30,'2024-02-21','2024-02-28','TRK000030','Delivered',9),
(31,31,'2024-03-26','2024-04-02','TRK000031','Delivered',10),
(32,32,'2024-04-11','2024-04-18','TRK000032','Delivered',11),
(33,33,'2024-05-16','2024-05-23','TRK000033','Delivered',12),
(34,34,'2024-06-21','2024-06-28','TRK000034','Delivered',13);
-- 10) offspring_tracking (20 rows - only from completed orders, all data validated)
INSERT INTO offspring_tracking(offspring_id, donor_id, recipient_id, date_of_birth, gender, reported_by, report_date)
VALUES
(1,1,1,'2024-10-15','Female','Harmony Fertility','2024-10-20'),
(2,2,2,'2025-03-20','Male','Hope Reproductive','2025-03-25'),
(3,3,3,'2024-06-05','Female','Sunrise Clinic','2024-06-10'),
(4,1,11,'2024-09-12','Male','Harmony Fertility','2024-09-18'),
(5,3,11,'2024-09-12','Female','Harmony Fertility','2024-09-18'),
(6,5,12,'2025-01-05','Male','NewEra IVF','2025-01-12'),
(7,7,12,'2025-01-05','Female','NewEra IVF','2025-01-12'),
(8,9,12,'2025-01-05','Male','NewEra IVF','2025-01-12'),
-- New offspring from historical completed orders #23-34 (12 rows)
(9,4,4,'2024-03-25','Male','LifeBirth Center','2024-03-30'),
(10,6,5,'2024-04-30','Female','Gentle Care Clinic','2024-05-05'),
(11,8,6,'2024-05-20','Male','Gentle Care Clinic','2024-05-25'),
(12,10,7,'2024-06-15','Female','BrightFuture Center','2024-06-20'),
(13,2,8,'2024-07-23','Male','Serene Health','2024-07-28'),
(14,4,9,'2024-08-28','Female','Emerald Fertility','2024-09-02'),
(15,6,10,'2024-11-05','Male','Harbor Clinic','2024-11-10'),
(16,8,14,'2024-11-25','Female','Hope Reproductive','2024-11-30'),
(17,10,15,'2024-12-30','Male','Emerald Fertility','2025-01-04'),
(18,12,17,'2025-01-15','Female','Manhattan Reproductive','2025-01-20'),
(19,14,18,'2025-02-20','Male','Manhattan Reproductive','2025-02-25'),
(20,15,19,'2025-03-25','Female','Tokyo Fertility Hospital','2025-03-30');
-- 11) donor_clinics (26 rows - many-to-many relationships)
INSERT INTO donor_clinics(donor_id, clinic_id, note)
VALUES
(1,1,'Preferred - Top performer'),
(1,2,'VIP - Highly requested'),
(1,5,'Partnership'),
(1,11,'Exclusive'),
(1,12,'Referral'),
(1,13,'International'),
(2,1,'Regular supplier'),
(2,2,'Active partnership'),
(2,6,'Referral'),
(2,10,'VIP'),
(3,1,'Premium donor'),
(3,3,'Preferred'),
(3,7,'Partnership'),
(3,14,'International'),
(5,4,'Regular'),
(5,8,'Active'),
(7,5,'Regular'),
(7,9,'Partnership'),
(8,6,'VIP'),
(8,10,'Preferred'),
(16,13,'Exclusive - Athlete'),
(16,16,'International VIP'),
(18,14,'International PhD'),
(18,18,'Partnership'),
(20,15,'Regular'),
(20,17,'International');

-- 12) audit_logs: Auto-generated by triggers (fn_audit_log), no manual insertion needed
-- The triggers will automatically log INSERT/UPDATE/DELETE operations on core tables

-- Reset all SERIAL sequences to avoid ID conflicts
SELECT setval('staff_staff_id_seq', (SELECT MAX(staff_id) FROM staff));
SELECT setval('donors_donor_id_seq', (SELECT MAX(donor_id) FROM donors));
SELECT setval('recipients_recipient_id_seq', (SELECT MAX(recipient_id) FROM recipients));
SELECT setval('partner_clinics_clinic_id_seq', (SELECT MAX(clinic_id) FROM partner_clinics));
SELECT setval('sperm_vials_vial_id_seq', (SELECT MAX(vial_id) FROM sperm_vials));
SELECT setval('medical_screenings_screening_id_seq', (SELECT MAX(screening_id) FROM medical_screenings));
SELECT setval('orders_order_id_seq', (SELECT MAX(order_id) FROM orders));
SELECT setval('order_items_order_item_id_seq', (SELECT MAX(order_item_id) FROM order_items));
SELECT setval('shipments_shipment_id_seq', (SELECT MAX(shipment_id) FROM shipments));
SELECT setval('offspring_tracking_offspring_id_seq', (SELECT MAX(offspring_id) FROM offspring_tracking));
-- audit_logs sequence is auto-managed by triggers

COMMIT;
